package com.apt.dao;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.apt.common.AptException;
import com.apt.common.DBUtility;
import com.apt.domain.UserMaster;

public class LoadUsersDao extends BaseConnection {

	public LoadUsersDao(Connection conn) {
		super(conn);
	}

	public int getRoleId(String roleName) throws AptException {
		int roleId;

		try {
			PreparedStatement ps = connection
					.prepareStatement("SELECT ROLE_ID FROM ROLE_MASTER WHERE NAME = ? ");
			ps.setString(1, roleName);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				roleId = rs.getInt("ROLE_ID");
			} else {
				throw new AptException(
						"No role defined for the given role name - " + roleName);
			}
			rs.close();
		} catch (SQLException se) {
			se.printStackTrace();
			throw new AptException(
					"unable to get role id from role master table");
		} finally {
			DBUtility.cleanUp(connection);
		}
		return roleId;
	}

	public String[] getDepartmentName(int departmentId) throws AptException {

		String[] departmentDetails = new String[2];
		try {
			PreparedStatement ps = connection
					.prepareStatement("SELECT NAME,DESCRIPTION FROM DEPARTMENT_MASTER WHERE DEPARTMENT_ID = ? ");
			ps.setInt(1, departmentId);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				departmentDetails[0] = rs.getString("DESCRIPTION");
				departmentDetails[1] = rs.getString("NAME");
			} else {
				throw new AptException(
						"no department name for the given department id - "
								+ departmentId + " in department master table");
			}

			rs.close();
		} catch (SQLException se) {
			se.printStackTrace();
			throw new AptException(
					"unable to get department name for the given department id - "
							+ departmentId + " from department master table");
		} finally {
			DBUtility.cleanUp(connection);
		}

		return departmentDetails;
	}

	public void insertUsers(List<UserMaster> userList) throws AptException {
		try {
			
			PreparedStatement ps = connection
					.prepareStatement("INSERT INTO USER_MASTER (USER_ID, COLLEGE_USER_ID, FIRST_NAME,PASSWORD,ROLE_ID,INSTITUTION_ID,DEPARTMENT)VALUES(?,?,?,?,?,?,? )");
			for (UserMaster u : userList) {
				ps.setString(1, u.getUserId());
				ps.setString(2, u.getCollegeId());
				ps.setString(3, u.getFirstName());
				ps.setString(4, u.getPassword());
				ps.setInt(5, u.getRoleId());
				ps.setInt(6, u.getInstitutionId());
				ps.setString(7, u.getDepartment());
				//System.out.println("user id - " + u.getUserId());
				ps.addBatch();
			}
			
			ps.executeBatch();
			ps.close();
			connection.commit();
		} catch (SQLException se) {
			se.printStackTrace();
			throw new AptException(
					"unable to insert users into user master table");
		}finally{
			DBUtility.cleanUp(connection);
		}

	}

}
